BEGIN;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE IF NOT EXISTS evaluation_report_header (
  id UUID PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_by TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  adopted_version_id UUID REFERENCES evaluation_report(id) ON DELETE RESTRICT
);
ALTER TABLE evaluation_report_header ALTER COLUMN id SET DEFAULT uuid_generate_v4();
ALTER TABLE IF EXISTS evaluation_report
  ADD COLUMN IF NOT EXISTS report_id UUID,
  ADD COLUMN IF NOT EXISTS version_no INTEGER,
  ADD COLUMN IF NOT EXISTS adopted BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS parent_version_id UUID REFERENCES evaluation_report(id) ON DELETE SET NULL;
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'chk_evaluation_report_adopted_final'
      AND conrelid = 'evaluation_report'::regclass
  ) THEN
    ALTER TABLE evaluation_report
      ADD CONSTRAINT chk_evaluation_report_adopted_final
      CHECK (adopted = FALSE OR status = 'final');
  END IF;
END $$;
CREATE UNIQUE INDEX IF NOT EXISTS uq_evaluation_report_version ON evaluation_report(report_id, version_no);
CREATE UNIQUE INDEX IF NOT EXISTS uq_evaluation_report_adopted ON evaluation_report(report_id) WHERE adopted = TRUE;
CREATE INDEX IF NOT EXISTS idx_evaluation_report_report ON evaluation_report(report_id);
CREATE INDEX IF NOT EXISTS idx_evaluation_report_status ON evaluation_report(status);
CREATE INDEX IF NOT EXISTS idx_evaluation_report_template ON evaluation_report(template_version_code);
CREATE SEQUENCE IF NOT EXISTS evaluation_report_code_seq;
WITH rows AS (
  SELECT id, title, created_by, created_at, updated_by, updated_at
  FROM evaluation_report
  WHERE report_id IS NULL
), ins AS (
  INSERT INTO evaluation_report_header(id, code, name, created_by, created_at, updated_by, updated_at)
  SELECT rows.id,
         'RPT-' || LPAD(nextval('evaluation_report_code_seq')::text, 6, '0'),
         rows.title,
         rows.created_by,
         rows.created_at,
         rows.updated_by,
         rows.updated_at
  FROM rows
  ON CONFLICT (id) DO NOTHING
  RETURNING id
)
UPDATE evaluation_report er
SET report_id = er.id,
    version_no = COALESCE(er.version_no, 1),
    adopted = FALSE
WHERE er.report_id IS NULL;
COMMIT;